IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[UpdateClinicalTrialBudgetStaffIdForTasks]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[UpdateClinicalTrialBudgetStaffIdForTasks]
GO
CREATE PROCEDURE [dbo].[UpdateClinicalTrialBudgetStaffIdForTasks] (
	@oldStaffId int, 
	@budgetTaskId int, 
	@budgetIdOld int, 
	@budgetIdNew int
)
AS
BEGIN
	SET NOCOUNT ON   

	IF EXISTS ( SELECT null FROM ClinicalTrialBudgetTask WHERE Id = @budgetTaskId )
	BEGIN
	-- Update
    
	UPDATE ClinicalTrialBudgetTask
	SET 	ClinicalTrialBudgetStaffId = (Select Id From ClinicalTrialBudgetStaff where StudyStaffRoleCode = (select StudyStaffRoleCode FROM ClinicalTrialBudgetStaff WHERE id = @oldStaffId and ClinicalTrialBudgetId = @budgetIdOld) and ClinicalTrialBudgetId = @budgetIdNew	)	
	WHERE Id = @budgetTaskId 

	IF @@ERROR <> 0
		BEGIN
			RAISERROR ('Failed to update clinical trial budget task. Error = %s.', 16, 1, @@ERROR)
			RETURN
		END
	END
END 
GO